<!DOCTYPE html>
<html>
<head>
    <title>10.触发器</title>

    <meta charset="UTF-8"/>

    <meta content="text/html; charset=utf-8" http-equiv="Content-Type"/>

    <meta name="HandheldFriendly" content="true"/>

    <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no"/>

    <meta name="apple-mobile-web-app-capable" content="yes"/>

    <meta name="apple-mobile-web-app-status-bar-style" content="black"/>

    <link href="./asset/farbox/basic.css" type="text/css" rel="stylesheet"/>
    <link href="./asset/farbox/main.css" type="text/css" rel="stylesheet"/>

    <style type="text/css">
        
            span.md_line{margin-bottom:0.5em; display:block; line-height:1.89}
            .md_line br{ display: none;}
            
    </style>

    <script>
        var flowchat_options = {
            'x': 0, 'y': 0, 'line-width': 1, 'line-length': 50, 'text-margin': 10, 'font-size': 13,
            'font-color': '#3c3c3c', 'line-color': '#666666', 'element-color': '#666666', 'fill': 'transparent',
            'yes-text': 'yes', 'no-text': 'no', 'arrow-end': 'block', 'class': 'flowchart', 'scale': 1,
            'symbols': { 'start': {}, 'end': {}, 'condition': {}, 'inputoutput': {}, 'operation': {}, 'subroutine': {}}
        }
    </script>
    <script type="text/javascript" src="./asset/other/raphael-min.js"></script>
    <script type="text/javascript" src="./asset/other/flowchart.js"></script>
    <script type="text/javascript" src="./asset/other/echarts.min.js"></script>


</head>

<body>
    
        <a href="./index.html" id="go_to_home"> &lt; home </a>
    
    <div id="sidebar">
        <ul id="sidebar_body">
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./001.软件下载安装配置.html" class="">第一章 软件下载安装配置</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./002.数据库操作.html" class="">002.数据库操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./003.数据表操作.html" class="">003.数据表操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./004.PHP与sql server连接.html" class="">004.PHP与sql server连接</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./005.数据的增删改.html" class="">005.数据的增删改</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./006.数据操作-简单查询.html" class="">006.数据操作-简单查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./007.高级查询.html" class="">07.高级查询-排序 多表联合 聚合与分组 限制条数</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./008.多表连接查询.html" class="">08.多表连接查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./009.子查询 ANY ALL [NOT] IN [NOT]EXISTS.html" class="">09.子查询 ANY ALL [NOT] IN [NOT]EXISTS</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./010.事务-索引-视图.html" class="">010.事务-索引-视图</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./011.T-SQL.html" class="">011.T-SQL学习</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./012.存储过程.html" class="">012.存储过程</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./013.触发器.html" class="selected">10.触发器</a>
                </li>
            

            
        
        </ul>
    </div>

    <div id="main">
        <div class="content_body">
            <h1 class="title"> 10.触发器 </h1>
            <div class="doc_post"> <p class="md_block">

<div class="toc"><ul>
<li>
<a href="#toc_0">10.触发器</a>
<ul>
<li>
<a href="#toc_1">概念</a>
</li>
<li>
<a href="#toc_2">触发器和存储过程的区别:</a>
</li>
<li>
<a href="#toc_3">+ 一:触发器的优点</a>
</li>
<li>
<a href="#toc_4">二:触发器的作用</a>
</li>
<li>
<a href="#toc_5">触发器的工作原理</a>
</li>
<li>
<a href="#toc_6">练习: INSTEAD OF 判断是哪种触发器</a>
</li>
</ul>
</li>
</ul>
</div>
</p>


<h2 id="toc_1">概念</h2>

<p class="md_block">
    <span class="md_line">  触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。</span>
</p>


<h2 id="toc_2">触发器和存储过程的区别:</h2>

<p class="md_block">
    <span class="md_line">  触发器与存储过程的区别是运行方式的不同，触发器不能执行EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发执行而存储过程需要用户，应用程序或者触发器来显示地调用并执行。</span>
</p>


<h2 id="toc_3">+ 一:触发器的优点</h2>

<p class="md_block">
    <span class="md_line"> 1.触发器是自动的。当对表中的数据做了任何修改之后立即被激活。<br /></span>
    <span class="md_line"> 2.触发器可以通过数据库中的相关表进行层叠修改。<br /></span>
    <span class="md_line"> 3.触发器可以强制限制。这些限制比用CHECK约束所定义的更复杂。与CHECK约束不同的是，触发器可以引用其他表中的列。</span>
</p>


<h2 id="toc_4">二:触发器的作用</h2>

<p class="md_block">
    <span class="md_line"> 触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂参照完整性和数据的一致性，它能够对数据库中的相关表进行级联修改，提高比CHECK约束更复杂的的数据完整性，并自定义错误消息。触发器的主要作用主要有以下接个方面：</span>
</p>


<p class="md_block">
    <span class="md_line">强制数据库间的引用完整性<br /></span>
    <span class="md_line">级联修改数据库中所有相关的表，自动触发其它与之相关的操作<br /></span>
    <span class="md_line">跟踪变化，撤销或回滚违法操作，防止非法修改数据<br /></span>
    <span class="md_line">返回自定义的错误消息，约束无法返回信息，而触发器可以<br /></span>
    <span class="md_line">触发器可以调用更多的存储过程</span>
</p>


<h2 id="toc_5">触发器的工作原理</h2>

<p class="md_block">
    <span class="md_line">触发器触发时：</span>
</p>


<p class="md_block">
    <span class="md_line">系统自动在内存中创建deleted表或inserted表；<br /></span>
    <span class="md_line">只读，不允许修改，触发器执行完成后，自动删除。</span>
</p>


<p class="md_block">
    <span class="md_line">inserted表：<br /></span>
    <span class="md_line">临时保存了插入或更新后的记录行；<br /></span>
    <span class="md_line">可以从inserted表中检查插入的数据是否满足业务需求；<br /></span>
    <span class="md_line">如果不满足，则向用户发送报告错误消息，并回滚插入操作。　</span>
</p>


<p class="md_block">
    <span class="md_line">deleted表：<br /></span>
    <span class="md_line">临时保存了删除或更新前的记录行；<br /></span>
    <span class="md_line">可以从deleted表中检查被删除的数据是否满足业务需求；<br /></span>
    <span class="md_line">如果不满足，则向用户报告错误消息，并回滚插入操作。<br /></span>
    <span class="md_line">inserted表和deleted表对照：　</span>
</p>

<table>
 <thead><tr><th style="text-align:left">修改操作记录</th>
<th style="text-align:left">inserted表</th>
<th style="text-align:left">deleted表</th>
 </tr>
</thead>
 <tbody><tr><td style="text-align:left">增加(insert)记录 </td>
<td style="text-align:left">  存放新增的记录 </td>
<td style="text-align:left">   ............</td>
</tr>
<tr><td style="text-align:left">删除(deleted)记录 </td>
<td style="text-align:left"> ..............   </td>
<td style="text-align:left"> 存放被删除的记录</td>
</tr>
<tr><td style="text-align:left">修改(update)记录 </td>
<td style="text-align:left">  存放更新后的记录 </td>
<td style="text-align:left">  存放更新前的记录</td>
</tr>
</tbody> 
</table>
<!--block_code_end-->
<p class="md_block">
    <span class="md_line md_line_dom_embed"><img onerror="this.src='../_image/2018-11-02-09-15-11.jpg';this.onerror=null;"  class="" src="/_image/2018-11-02-09-15-11.jpg" alt="" title="" ><br /></span>
    <span class="md_line">语法</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">TRIGGER</span> <span class="k">trigger_name</span>
 <span class="k">ON</span> <span class="k">table_name</span>
 <span class="p">[</span><span class="k">WITH</span> <span class="n">ENCRYPTION</span><span class="p">]</span>
  <span class="k">FOR</span> <span class="o">|</span> <span class="k">AFTER</span> <span class="o">|</span> <span class="k">INSTEAD</span> <span class="k">OF</span> <span class="p">[</span><span class="k">DELETE</span><span class="p">,</span> <span class="k">INSERT</span><span class="p">,</span> <span class="k">UPDATE</span><span class="p">]</span>
 <span class="k">AS</span> 
  <span class="n">T</span><span class="o">-</span><span class="k">SQL</span><span class="err">语句</span>
<span class="k">GO</span>
<span class="c1">-- 其中FOR | AFTER 一样,是后触发器,而INSTEAD OF是前触发器</span>
<span class="c1">--with encryption 表示加密触发器定义的sql文本</span>
<span class="c1">--delete,insert,update指定触发器的类型</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_6">练习: INSTEAD OF 判断是哪种触发器</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 触发器</span>
<span class="c1">--INSTEAD OF 是前触发....原语句不会生效.你可以根据条件执行相应的语句....</span>

<span class="k">drop</span> <span class="k">trigger</span> <span class="n">tr_user_info</span>
<span class="k">ALTER</span> <span class="k">TRIGGER</span> <span class="n">tr_user_info</span>
<span class="k">ON</span> <span class="n">user_info</span>
<span class="k">INSTEAD</span> <span class="k">OF</span> <span class="k">DELETE</span><span class="p">,</span><span class="k">INSERT</span><span class="p">,</span><span class="k">UPDATE</span>
<span class="k">AS</span>
    <span class="c1">--insert into user_info_del SELECT * FROM deleted</span>
    <span class="c1">-- ROLLBACK</span>
    <span class="c1">--print &#39;欢迎删除&#39;</span>
    <span class="k">declare</span> <span class="o">@</span><span class="n">del</span> <span class="k">as</span> <span class="nb">bit</span> <span class="o">=</span> <span class="mi">0</span>
    <span class="k">declare</span> <span class="o">@</span><span class="n">ins</span> <span class="k">as</span> <span class="nb">bit</span> <span class="o">=</span> <span class="mi">0</span>
    
    <span class="k">if</span> <span class="k">exists</span><span class="p">(</span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">inserted</span><span class="p">)</span> <span class="k">set</span> <span class="o">@</span><span class="n">ins</span> <span class="o">=</span> <span class="mi">1</span>
    <span class="k">if</span> <span class="k">exists</span><span class="p">(</span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">deleted</span><span class="p">)</span> <span class="k">set</span> <span class="o">@</span><span class="n">del</span> <span class="o">=</span> <span class="mi">1</span>
    <span class="k">if</span><span class="p">(</span><span class="o">@</span><span class="n">del</span> <span class="o">=</span> <span class="mi">1</span> <span class="k">and</span> <span class="o">@</span><span class="n">ins</span> <span class="o">=</span> <span class="mi">1</span><span class="p">)</span> 
    <span class="k">BEGIN</span>
        <span class="n">print</span> <span class="s1">&#39;你执行的是update语句!&#39;</span>
        <span class="k">select</span> <span class="o">*</span> <span class="k">from</span> <span class="n">deleted</span>
        <span class="k">select</span> <span class="o">*</span> <span class="k">from</span> <span class="n">inserted</span>
        <span class="k">DELETE</span> <span class="k">FROM</span> <span class="n">user_info</span> <span class="k">WHERE</span> <span class="n">uid</span> <span class="k">IN</span><span class="p">(</span><span class="k">SELECT</span> <span class="n">uid</span> <span class="k">FROM</span> <span class="n">deleted</span><span class="p">)</span>
        <span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">user_info</span> <span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">inserted</span>
    <span class="k">END</span>
    <span class="k">else</span> <span class="k">if</span> <span class="p">(</span><span class="o">@</span><span class="n">del</span> <span class="o">=</span> <span class="mi">1</span><span class="p">)</span>
    <span class="k">BEGIN</span>
        <span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">user_info_del</span> <span class="k">select</span> <span class="o">*</span> <span class="k">from</span> <span class="n">deleted</span>
        <span class="k">DELETE</span> <span class="k">FROM</span> <span class="n">user_info</span> <span class="k">WHERE</span> <span class="n">uid</span> <span class="k">IN</span><span class="p">(</span><span class="k">SELECT</span> <span class="n">uid</span> <span class="k">FROM</span> <span class="n">deleted</span><span class="p">)</span>
        <span class="n">print</span> <span class="s1">&#39;你执行的是delete语句!&#39;</span>
    <span class="k">END</span>
    <span class="k">else</span> <span class="k">if</span> <span class="p">(</span><span class="o">@</span><span class="n">ins</span> <span class="o">=</span> <span class="mi">1</span><span class="p">)</span>
    <span class="k">BEGIN</span>
        <span class="n">print</span> <span class="s1">&#39;你执行的是insert语句!&#39;</span>
        <span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">user_info</span> <span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">inserted</span>
    <span class="k">END</span>
    <span class="k">else</span> <span class="n">print</span> <span class="s1">&#39;不知道你执行的是什么语句,但对表没有任何引响!!!&#39;</span>
<span class="k">GO</span>
</pre></div>

<!--block_code_end-->
<p class="md_block">
    <span class="md_line">.</span>
</p> </div>
        </div>

    </div>





<script>
    // auto focus for menu
    window.onload = function(){
        var sidebar = document.getElementById('sidebar');
        var current = document.getElementsByClassName('selected')[0];
        if (current && sidebar){
            sidebar.scrollTop = current.offsetTop - sidebar.clientHeight/2 + 100
        }
    };
</script>




</body>

</html>